﻿using System;
using System.Data;
using System.Configuration;
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
using LdExpress;
using System.IO;
using System.Globalization;

public partial class _Default : System.Web.UI.Page
{
    public string ContainerPage = "./PriceAreas.aspx";
    public string strCondition = "";
    public int NumOfItemsPerPage = 500;
    public string strPager = "";
    public string strError = "";
    public string strMess = "";
    public int nPermissions = 0;
    public int nPage = 1;
    public string strPageID = "page_number";
    string strExcluded = "";
    public string strPartQuery = "";
    public string strInforServiceAreas = "PRICE AREAS";
    string LastGroup = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {

            if (Request.QueryString[strPageID] != null)
                nPage = int.Parse(Request.QueryString[strPageID]);
        }
        catch
        {
            nPage = 1;
        }
        if (nPage <= 0) nPage = 1;
        if (Session["logined_id"] != null)
        {
            if (Session["PriceAreas"] != "") nPermissions = int.Parse(Session["PriceAreas"].ToString());
            if (nPermissions <= 0) Response.Redirect("Login.aspx");
        }
        else
            Response.Redirect("Login.aspx");
        if (!Page.IsPostBack)
        {
            if (nPermissions < 7)
            {
                btnShowAddNewBox.Visible = false;
                btnInit.Visible = false;
            }
            if (Request.QueryString["action"] == "delete" && nPermissions >= 9)
            {
                PriceAreas obj = new PriceAreas();
                obj.ID = long.Parse(Request.QueryString["ID"]);
                obj.Delete(GV.cn, GV.trans);
                UserObjectsLog.SaveObjectActionLog(long.Parse(Session["logined_id"].ToString()), "PriceAreas", "Delete", GV.cn, GV.trans);
                Response.Redirect("PriceAreas.aspx" + ((Request.QueryString["page_number"] != null && Request.QueryString["page_number"].ToString() != "") ? "?page_number=" + Request.QueryString["page_number"].ToString() : ""));
            }
            if (Request.QueryString["action"] == "public")
            {
                PriceAreas obj = new PriceAreas();
                obj.ID = long.Parse(Request.QueryString["ID"].ToString());
                obj.Load(GV.cn, GV.trans);
                string tempDt = "";
                string tempRate = "";
                if (obj.DC20.Length > 10)
                {
                    tempDt = obj.DC20.Substring(obj.DC20.Length - 10).Trim();
                    tempRate = obj.DC20.Split('_')[0].ToString();
                    GoodPricesNotice.PublicGoodPriceNoticeFromAreas(obj, tempDt, tempRate, "20'DC");
                }
                if (obj.DC40.Length > 10)
                {
                    tempDt = obj.DC40.Substring(obj.DC40.Length - 10).Trim();
                    tempRate = obj.DC40.Split('_')[0].ToString();
                    GoodPricesNotice.PublicGoodPriceNoticeFromAreas(obj, tempDt, tempRate, "40'DC");
                }
                if (obj.HC40.Length > 10)
                {
                    tempDt = obj.HC40.Substring(obj.HC40.Length - 10).Trim();
                    tempRate = obj.HC40.Split('_')[0].ToString();
                    GoodPricesNotice.PublicGoodPriceNoticeFromAreas(obj, tempDt, tempRate, "40'HC");
                }
                if (obj.RF20.Length > 10)
                {
                    tempDt = obj.RF20.Substring(obj.RF20.Length - 10).Trim();
                    tempRate = obj.RF20.Split('_')[0].ToString();
                    GoodPricesNotice.PublicGoodPriceNoticeFromAreas(obj, tempDt, tempRate, "20'RF");
                }
                if (obj.RF40.Length > 10)
                {
                    tempDt = obj.RF40.Substring(obj.RF40.Length - 10).Trim();
                    tempRate = obj.RF40.Split('_')[0].ToString();
                    GoodPricesNotice.PublicGoodPriceNoticeFromAreas(obj, tempDt, tempRate, "40'RF");
                }
                Response.Redirect("PriceAreas.aspx" + ((Request.QueryString["page_number"] != null && Request.QueryString["page_number"].ToString() != "") ? "?page_number=" + Request.QueryString["page_number"].ToString() : ""));
            }
            LoadCarrDropDown();
            LoadPolDropDown();
            LoadPodDropDown();
            LoadGroupAreasDropDown();
            LoadBuildList();
        }
    }
    private void LoadBuildList()
    {
        #region Build List
        int nNumOfPages = 0;

        DataTable dtList;
        BuildList(NumOfItemsPerPage, nPage, out nNumOfPages, out dtList, GV.cn, GV.trans);
        if (nPage > nNumOfPages) nPage = 1;

        rptData.DataSource = dtList;
        rptData.DataBind();
        #endregion
        #region Build Pager
        CommonFunctions.BuildPager(Request, nNumOfPages, nPage, strPageID, ContainerPage, out strPager);
        #endregion
        loadDropDownServiceArea();
    }
    private void BuildList(int NumOfItemsPerPage, int PageNo, out int nNumOfPages, out DataTable dtList, MySqlConnection cn, MySqlTransaction trans)
    {
        DataSet ds = null;
        dtList = new DataTable();
        dtList.Columns.Add("ID");
        dtList.Columns.Add("Sort");
        dtList.Columns.Add("Index");
        dtList.Columns.Add("DC20");
        dtList.Columns.Add("DC40");
        dtList.Columns.Add("HC40");
        dtList.Columns.Add("RF20");
        dtList.Columns.Add("RF40");
        dtList.Columns.Add("Validity");
        dtList.Columns.Add("Note");
        dtList.Columns.Add("LastUpdate");
        dtList.Columns.Add("SerAreaID");
        dtList.Columns.Add("SerAreaName");
        dtList.Columns.Add("PolID");
        dtList.Columns.Add("PolName");
        dtList.Columns.Add("PodID");
        dtList.Columns.Add("PodName");
        dtList.Columns.Add("GroupAreas");
        dtList.Columns.Add("Action");
        nNumOfPages = 0;

        //string strOrderBy = "CarrName,LastUpdate Desc";
        if (Session["PriceAreasCarr"] != null && Session["PriceAreasCarr"].ToString() != "")
        {
            strCondition = " AND CarrID=" + Session["PriceAreasCarr"].ToString();
            string strConditionPO = "";
            if (Session["PriceAreasPol"] != null && Session["PriceAreasPol"].ToString() != "")
            {
                strConditionPO = " AND PolID=" + Session["PriceAreasPol"].ToString();
            }
            if (Session["PriceAreasPod"] != null && Session["PriceAreasPod"].ToString() != "")
            {
                strConditionPO = " AND PodID=" + Session["PriceAreasPod"].ToString();
            }
            if (strConditionPO != "")
            {
                strCondition += strConditionPO;
            }
            else
                strCondition = " AND 1=0";
        }
        else
            strCondition = " AND 1=0";
        string groupAreas = "";
        if (Session["PriceAreasGroupAreas"] != null && Session["PriceAreasGroupAreas"].ToString() != "")
        {
            groupAreas = Session["PriceAreasGroupAreas"].ToString();
        }
        else if (nPermissions <= 7)
            strCondition = " AND 1=0";
        PriceAreas.GetList(strCondition, groupAreas, "", NumOfItemsPerPage, PageNo, out nNumOfPages, out ds, GV.cn, GV.trans);

        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            btnsave.Visible = true;
            btnShowAddNewBox.Visible = true;
        }
        string strAllQueryString = "";
        foreach (string strKey in Request.QueryString.AllKeys)
        {
            if (strKey != null)
            {
                if ((strKey.ToLower() != "action") && (strKey.ToLower() != "id"))
                    strAllQueryString = strAllQueryString + "&" + strKey + "=" + Request.QueryString[strKey].ToString();
            }
        }

        if (strAllQueryString.Length >= 1)
            strAllQueryString = strAllQueryString.Substring(1);

        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            GetInforServiceAreas();
            int count = 0;
            if (Request.QueryString["page_number"] != null && Request.QueryString["page_number"].ToString() != "")
            {
                int pagenumber = 1;
                try
                {
                    pagenumber = int.Parse(Request.QueryString["page_number"]);
                    count = NumOfItemsPerPage * (pagenumber - 1);
                }
                catch { count = 0; }
            }
            DataSet objds = null;

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                objds = new DataSet();

                count++;
                string strID = dr["ID"].ToString();
                string strSTT = dr["Sort"].ToString();
                string strIndex = count.ToString();
                string strDC20 = dr["DC20"].ToString();
                string strDC40 = dr["DC40"].ToString();
                string strHC40 = dr["HC40"].ToString();
                string strRF20 = dr["RF20"].ToString();
                string strRF40 = dr["RF40"].ToString();
                string strValidity = dr["Validity"].ToString();
                string strNote = dr["Note"].ToString();
                string strPolID = dr["PolID"].ToString();
                string strPodID = dr["PodID"].ToString();
                string strPolName = dr["PolName"].ToString();
                string strPodName = dr["PodName"].ToString();
                string strCarrID = dr["CarrID"].ToString();
                string strCarrName = dr["CarrName"].ToString();
                string strSeviceAreasID = dr["ServiceAreaID"].ToString();
                string strSeviceAreasName = dr["ServiceAreaName"].ToString();
                string strGroupAreas = dr["ServiceAreaGroup"].ToString();

                string strLastUpdate = "";
                if (dr["LastUpdate"].ToString() != "")
                {
                    DateTime dt = DateTime.Parse(dr["LastUpdate"].ToString());
                    if (dt > DateTime.MinValue)
                        strLastUpdate = dt.ToString("ddMMyyyy");
                }
                string strAction = "";
                string strTemp = "";
                if (nPermissions >= 3)
                {
                    strAction = "<div class=\"changed\">";
                    //get public
                    if (dr["LastUpdate"].ToString().Length > 0)
                    {
                        DateTime dtLst = DateTime.Parse(dr["LastUpdate"].ToString());
                        if (dtLst.AddDays(7) >= DateTime.Now)
                        {
                            strAction += "<a href=\"javascript:doPublic('" + System.Web.HttpUtility.HtmlEncode(dr["ID"].ToString()) + "','" + System.Web.HttpUtility.HtmlEncode(strTemp) + "')\">Public - </a>";
                        }
                    }
                    strAction += "<a href=\"Schedules.aspx?PortToID=" + strPolID + "&PortFromID=" + strPodID + "&CarrName=" + strCarrName + "&SerArea=" + strGroupAreas + "\"  target=\"_blank\" >Schedules - </a>";
                }
                if (nPermissions > 7)
                {
                    strAction += "<a href=\"PriceAreas.aspx?action=delete&ID=" + dr["ID"].ToString() + "\"> Delete </a>";
                }
                strAction += "</div>";
                string[] strValues = new string[] { strID, strSTT, strIndex
                    , strDC20, strDC40, strHC40, strRF20, strRF40, strValidity, strNote, strLastUpdate
                    , strSeviceAreasID,strSeviceAreasName, strPolID,strPolName, strPodID,strPodName,strGroupAreas, strAction };
                dtList.Rows.Add(strValues);


            }
        }
    }
    //protected void btnSearch_Click(object sender, EventArgs e)
    //{


    //}
    public string GetCellTitle(object inputstring)
    {
        string title = "";
        if (inputstring != null && !string.IsNullOrEmpty(inputstring.ToString()))
        {
            string[] arrUpdate = inputstring.ToString().Split('_');
            if (arrUpdate.Length > 1)
                title = arrUpdate[1];
        }
        return title;
    }
    public string GetCellClass(object inputstring)
    {
        string className = "";
        if (inputstring != null && !string.IsNullOrEmpty(inputstring.ToString()))
        {
            string[] arrUpdate = inputstring.ToString().Split('_');
            if (arrUpdate.Length > 1)
            {
                try
                {
                    string tempDt = arrUpdate[1].Substring(arrUpdate[1].Length - 10).Trim();
                    DateTime dt = DateTime.ParseExact(tempDt, "dd-MM-yyyy", CultureInfo.InvariantCulture);
                    if (dt.AddDays(7) >= DateTime.Now)
                        className = "has-value-lastweek";
                    else if (dt.AddDays(14) >= DateTime.Now && dt.AddDays(7) < DateTime.Now)
                        className = "has-value-last2week";
                    else
                        className = "has-value";
                }
                catch
                {
                    className = "has-value";
                }
            }
        }
        return className;
    }
    public string GetCellValue(object inputstring, bool emptyIfNull = false)
    {
        string cellvalue = !emptyIfNull ? "No check" : "";
        if (inputstring != null && !string.IsNullOrEmpty(inputstring.ToString()))
        {
            string[] arrUpdate = inputstring.ToString().Split('_');
            cellvalue = arrUpdate[0];
        }
        return cellvalue;
    }
    protected void btnInit_Click(object sender, EventArgs e)
    {
        lblerror.Text = "";
        if (txtCarrNameSearch.Text.Trim().Length == 0)
        {
            lblerror.Text = "Tên hãng tàu chưa được chọn.";
            lblerror.Focus();
            return;
        }
        else
        {
            if (txtPOLSearch.Text.Trim().Length == 0)
            {
                if (txtPODSearch.Text.Trim().Length == 0)
                {
                    lblerror.Text = "Phải chọn POL hoặc POD để khởi tạo dữ liệu (Hàng xuất: chọn POL. Hàng nhập: chọn POD.)";
                    lblerror.Focus();
                    return;
                }
            }
            else
            {
                if (txtPODSearch.Text.Trim().Length > 0)
                {
                    lblerror.Text = "Chỉ được chọn POL hoặc POD để khởi tạo dữ liệu (Hàng xuất: chọn POL. Hàng nhập: chọn POD.)";
                    lblerror.Focus();
                    return;
                }
            }
        }

        Carrs carr = new Carrs();
        carr.Load(txtCarrNameSearch.Text.Trim(), GV.cn, GV.trans);
        if (carr.ID <= 0)
        {
            lblerror.Text = "Tên hãng tàu không hợp lệ.";
            lblerror.Focus();
            return;
        }
        long polID = 0, podID = 0;
        if (txtPOLSearch.Text.Trim().Length > 0)
        {
            Ports Pol = new Ports();
            Pol.Load(txtPOLSearch.Text.Trim(), GV.cn, GV.trans);
            if (Pol.ID <= 0)
            {
                lblerror.Text = "POL không hợp lệ.";
                lblerror.Focus();
                return;
            }
            polID = Pol.ID;
        }
        if (txtPODSearch.Text.Trim().Length > 0)
        {
            Ports Pod = new Ports();
            Pod.Load(txtPODSearch.Text.Trim(), GV.cn, GV.trans);
            if (Pod.ID <= 0)
            {
                lblerror.Text = "POD không hợp lệ.";
                lblerror.Focus();
                return;
            }
            podID = Pod.ID;
        }
        PriceAreas.InitializeData(carr.ID, polID, podID, GV.cn, GV.trans);
        Session["PriceAreasCarr"] = carr.ID;
        if (polID > 0)
        {
            Session["PriceAreasPol"] = polID;
            Session["PriceAreasPod"] = "";
        }
        if (podID > 0)
        {
            Session["PriceAreasPol"] = "";
            Session["PriceAreasPod"] = podID.ToString();
        }
        LoadCarrDropDown();
        LoadPolDropDown();
        LoadPodDropDown();
        LoadBuildList();
    }
    //Dropdown Carriers
    public void LoadCarrDropDown()
    {
        string sqlCommand = "SELECT DISTINCT CarrID,Carr.Name as CarrName FROM tbl_priceAreas INNER JOIN `tbl_carrs` Carr " +
            "ON tbl_priceAreas.carrID= Carr.ID Order By CarrName ASC";
        DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        ddCarr.Items.Add((new ListItem("Carriers", "")));
        ddCarr.AppendDataBoundItems = true;
        ddCarr.DataSource = ds.Tables[0];
        ddCarr.DataValueField = ds.Tables[0].Columns[0].ToString();
        ddCarr.DataTextField = ds.Tables[0].Columns[1].ToString();
        ddCarr.DataBind();
        if (Session["PriceAreasCarr"] != null && ds.Tables[0].Rows.Count > 0)
        {
            try
            {
                ddCarr.SelectedValue = Session["PriceAreasCarr"].ToString();
            }
            catch { }
        }
    }
    protected void ddCarr_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["PriceAreasCarr"] = ddCarr.SelectedValue;
        Session["PriceAreasCarrName"] = ddCarr.SelectedItem.Text;
        //LoadBuildList();
        Response.Redirect("PriceAreas.aspx" + ((Request.QueryString["page_number"] != null && Request.QueryString["page_number"].ToString() != "") ? "?page_number=" + Request.QueryString["page_number"].ToString() : ""));
    }
    //DropDown POL
    public void LoadPolDropDown()
    {
        string sqlCommand = "SELECT DISTINCT PolID,POL.Name as PolName FROM tbl_priceAreas INNER JOIN `tbl_ports` POL " +
            "ON tbl_priceAreas.PolID= POL.ID Where 1=1 ";
        if (Session["PriceAreasCarr"] != null && Session["PriceAreasCarr"].ToString() != "")
            sqlCommand += " AND CarrID=" + Session["PriceAreasCarr"].ToString();
        sqlCommand += " Order By PolName ASC";
        DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        ddPol.Items.Add((new ListItem("POL", "")));
        ddPol.AppendDataBoundItems = true;
        ddPol.DataSource = ds.Tables[0];
        ddPol.DataValueField = ds.Tables[0].Columns[0].ToString();
        ddPol.DataTextField = ds.Tables[0].Columns[1].ToString();
        ddPol.DataBind();
        if (Session["PriceAreasPol"] != null && ds.Tables[0].Rows.Count > 0)
        {
            try
            {
                ddPol.SelectedValue = Session["PriceAreasPol"].ToString();
            }
            catch { }
        }
    }
    protected void ddPol_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["PriceAreasPol"] = ddPol.SelectedValue;
        Session["PriceAreasPolName"] = ddPol.SelectedItem.Text;
        Session["PriceAreasPod"] = "";
        ddPod.SelectedValue = "";
        LoadBuildList();
    }
    //DropDown POD
    public void LoadPodDropDown()
    {
        string sqlCommand = "SELECT DISTINCT PodID,POD.Name as PodName FROM tbl_priceAreas INNER JOIN `tbl_ports` POD " +
            "ON tbl_priceAreas.PodID= POD.ID where 1=1";
        if (Session["PriceAreasCarr"] != null && Session["PriceAreasCarr"].ToString() != "")
            sqlCommand += " AND CarrID=" + Session["PriceAreasCarr"].ToString();
        sqlCommand += " Order By PodName ASC";
        DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        ddPod.Items.Add((new ListItem("POD", "")));
        ddPod.AppendDataBoundItems = true;
        ddPod.DataSource = ds.Tables[0];
        ddPod.DataValueField = ds.Tables[0].Columns[0].ToString();
        ddPod.DataTextField = ds.Tables[0].Columns[1].ToString();
        ddPod.DataBind();
        if (Session["PriceAreasPod"] != null && ds.Tables[0].Rows.Count > 0)
        {
            try
            {
                ddPod.SelectedValue = Session["PriceAreasPod"].ToString();
            }
            catch { }
        }
    }
    protected void ddPod_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["PriceAreasPod"] = ddPod.SelectedValue;
        Session["PriceAreasPodName"] = ddPod.SelectedItem.Text;
        Session["PriceAreasPol"] = "";
        ddPol.SelectedValue = "";
        LoadBuildList();
    }
    private void GetInforServiceAreas()
    {
        strInforServiceAreas = "PRICE AREAS";
        if (Session["PriceAreasCarrName"] != null && Session["PriceAreasCarrName"].ToString().ToLower() != "carriers")
        {
            if (Session["PriceAreasPol"] != null && Session["PriceAreasPol"].ToString() != "")
                strInforServiceAreas = "EXPORT: " + Session["PriceAreasCarrName"].ToString() + " - " + Session["PriceAreasPolName"].ToString();
            if (Session["PriceAreasPod"] != null && Session["PriceAreasPod"].ToString() != "")
                strInforServiceAreas = "IMPORT: " + Session["PriceAreasCarrName"].ToString() + " - " + Session["PriceAreasPodName"].ToString();
        }
        strInforServiceAreas = strInforServiceAreas.ToUpper();
    }
    //DropDown GroupAreas
    public void LoadGroupAreasDropDown()
    {
        string sqlCommand = "SELECT DISTINCT GroupAreas FROM tbl_ServiceAreas,tbl_priceAreas " +
            " where tbl_priceAreas.ServiceAreaID=tbl_ServiceAreas.ID";
        if (Session["PriceAreasCarr"] != null && Session["PriceAreasCarr"].ToString() != "")
            sqlCommand += " AND CarrID=" + Session["PriceAreasCarr"].ToString();
        sqlCommand += " Order By GroupAreas ASC";
        DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        ddGroupAreas.Items.Add((new ListItem("GROUP AREAS", "")));
        ddGroupAreas.AppendDataBoundItems = true;
        ddGroupAreas.DataSource = ds.Tables[0];
        ddGroupAreas.DataValueField = ds.Tables[0].Columns[0].ToString();
        ddGroupAreas.DataTextField = ds.Tables[0].Columns[0].ToString();
        ddGroupAreas.DataBind();
        if (Session["PriceAreasGroupAreas"] != null && ds.Tables[0].Rows.Count > 0)
        {
            try
            {
                ddGroupAreas.SelectedValue = Session["PriceAreasGroupAreas"].ToString();
            }
            catch { }
        }
    }
    protected void ddGroupAreas_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["PriceAreasGroupAreas"] = ddGroupAreas.SelectedValue;
        LoadBuildList();
    }
    protected string RenderGroup(string Group)
    {
        if (Group == this.LastGroup)
            return "";
        // *** Group has changed
        this.LastGroup = Group;
        return "<tr class='groupheader'><td colspan='12' class='thheader'>" + Group + "</td></tr>";
    }
    protected void btnAddNew_Click(object sender, System.EventArgs e)
    {
        lblerror.Text = "";
        if (int.Parse(ddNewServiceArea.SelectedValue) == 0)
        {
            lblerror.Text = "Bạn chưa chọn Service Area.";
            ddNewServiceArea.Focus();
            return;
        }
        if (Session["PriceAreasCarr"] != null && Session["PriceAreasCarr"].ToString() != "")
        {
            long pol = 0, pod = 0;
            if (Session["PriceAreasPol"] != null && Session["PriceAreasPol"].ToString() != "")
            {
                pol = long.Parse(Session["PriceAreasPol"].ToString());
            }
            if (Session["PriceAreasPod"] != null && Session["PriceAreasPod"].ToString() != "")
            {
                pod = long.Parse(Session["PriceAreasPod"].ToString());
            }


            PriceAreas obj = new PriceAreas();
            obj.CarrID = long.Parse(Session["PriceAreasCarr"].ToString());
            obj.PolID = pol;
            obj.PodID = pod;
            obj.ServiceAreaID = long.Parse(ddNewServiceArea.SelectedValue);
            obj.Save(GV.cn, GV.trans);
            Response.Redirect("PriceAreas.aspx");
        }
    }
    protected void loadDropDownServiceArea()
    {
        if ((Session["PriceAreasCarr"] != null && Session["PriceAreasCarr"].ToString() != "")
            && ((Session["PriceAreasPol"] != null && Session["PriceAreasPol"].ToString() != "")
            || (Session["PriceAreasPod"] != null && Session["PriceAreasPod"].ToString() != ""))
            )
        {
            string sql = "";
            DataSet ds = null;
            sql = "SELECT ID,Name FROM tbl_ServiceAreas where 1=1";
            sql += " AND ID not in(Select ServiceAreaID From tbl_PriceAreas Where CarrID=" + Session["PriceAreasCarr"].ToString();
            if (Session["PriceAreasPol"] != null && Session["PriceAreasPol"].ToString() != "")
            {
                sql += " AND PolID=" + Session["PriceAreasPol"].ToString();
            }
            if (Session["PriceAreasPod"] != null && Session["PriceAreasPod"].ToString() != "")
            {
                sql += " AND PodID=" + Session["PriceAreasPod"].ToString();
            }
            sql += ")";
            ds = DBModule.ExecuteQuery(sql, GV.cn, GV.trans);
            DataTable dtb = ds.Tables[0];
            ddNewServiceArea.DataSource = dtb;
            ddNewServiceArea.DataValueField = dtb.Columns[0].ToString();
            ddNewServiceArea.DataTextField = dtb.Columns[1].ToString();
            ddNewServiceArea.DataBind();
            ddNewServiceArea.Items.Insert(0, new ListItem("SERVICE AREAS", "0"));
        }
    }
}
